USE [LAFDC]
GO
/****** Object:  StoredProcedure [dbo].[spGetRoutage]    Script Date: 11/23/2009 23:02:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGetRoutage]
AS
BEGIN
	SET NOCOUNT ON;

		select	adh.ad + '-' + adh.prev_id as 'ID',
				upper(section) as 'Section', 
				[civ-sexe] + ' ' + upper(NOM) + ' ' + PRENOM as 'Destinataire', 
				upper(adr.adresse_1) as 'Adresse', 
				upper(adr.adresse_2) as 'Complement d''Adresse', 
				upper(adr.CodePostal) + ' ' + upper(adr.Ville) as 'Ville_cp', 
				adh.Journal
		  from adherent adh
		  join Personne prsn
			on adh.prev_id = prsn.prev_id
		  join Adresse adr
			on adh.prev_id = adr.prev_id
		 where prsn.sfam != 'Enfant' and prsn.sfam!='Conjoint(e)'
		   and adh.journal = 1
		   and adh.ad != 'DEMISSION'
		 union
		select asso + '-' + prev_id, code, upper(destinataire), upper(address_1), upper(address_2), upper(ville_cp), quantite
		  from Routage
		 order by ID

END
